{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:26:53.878055Z",
     "iopub.status.busy": "2023-11-13T13:26:53.877853Z",
     "iopub.status.idle": "2023-11-13T13:26:54.738908Z",
     "shell.execute_reply": "2023-11-13T13:26:54.738277Z",
     "shell.execute_reply.started": "2023-11-13T13:26:53.878023Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "import os\n",
    "\n",
    "stage = 'B'\n",
    "root = '../../../contest'\n",
    "\n",
    "df_train = pd.read_csv(os.path.join(root, 'train/GSLD_MB_TRNFLW.csv'))\n",
    "df_test = pd.read_csv(os.path.join(root, '{}/GSLD_MB_TRNFLW_{}.csv'.format(stage, stage)))\n",
    "\n",
    "save_path = '../data'\n",
    "\n",
    "end_date_train = datetime(1996,7,5)\n",
    "end_date_test = datetime(1996,9,4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:26:54.740265Z",
     "iopub.status.busy": "2023-11-13T13:26:54.740072Z",
     "iopub.status.idle": "2023-11-13T13:26:54.752296Z",
     "shell.execute_reply": "2023-11-13T13:26:54.751662Z",
     "shell.execute_reply.started": "2023-11-13T13:26:54.740240Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# trn_code_list = df_train.TRANSCODE.value_counts(ascending=False).index\n",
    "# trn_code_dic = {trn_code_list[i]:i for i,_ in enumerate(trn_code_list)}\n",
    "def prepro(df, end_date, offset):\n",
    "    df.AMOUNT.fillna(df.AMOUNT.mean())\n",
    "    df.AMOUNT = df.AMOUNT.apply(lambda x: round(pow(x/3.12,3),2))\n",
    "#     df.TRANSCODE = df.TRANSCODE.map(trn_code_dic)\n",
    "    \n",
    "    df['DATE'] = pd.to_datetime(df['DATE'], format='%Y%m%d')\n",
    "    df['weekday'] = (df['DATE'].dt.dayofweek+7+offset) % 7 # 当周星期几 \n",
    "    \n",
    "    tmp_df = df.groupby('CUST_NO').agg(\n",
    "        trnflw_trn_cd_nunique = ('TRANSCODE', 'nunique'),\n",
    "        trnflw_last_date = ('DATE', 'max'),\n",
    "        trnflw_mean_amount = ('AMOUNT', 'mean'),\n",
    "        trnflw_sum_amount = ('AMOUNT', 'sum'),\n",
    "        trnflw_exist_weekend = ('weekday', 'max'), # 交易记录是否存在周末\n",
    "        trnflw_cust_cnt = ('weekday', 'count') # 总条数\n",
    "    ).reset_index()\n",
    "    \n",
    "    tmp_df['trnflw_last_date'] = pd.to_datetime(tmp_df['trnflw_last_date'], format='%Y%m%d')\n",
    "    tmp_df['trnflw_weekday'] = (tmp_df['trnflw_last_date'].dt.dayofweek+7+offset)% 7 # 当周星期几\n",
    "    tmp_df['trnflw_last_date'] = (end_date - tmp_df['trnflw_last_date']).dt.days\n",
    "#     tmp_df['trnflw_is_weekend'] = tmp_df['trnflw_weekday'].apply(lambda x: 1 if x==5 or x==6 else 0) # 最大交易日期是否是周末\n",
    "    tmp_df['trnflw_exist_weekend'] = tmp_df['trnflw_exist_weekend'].apply(lambda x: 1 if x==6 or x==5 else 0) # 交易记录是否存在周末\n",
    "    \n",
    "    # 计算周末交易的条数\n",
    "    tmp_df2 = df[((df.weekday==5)|(df.weekday==6))].groupby('CUST_NO').agg(\n",
    "        trnflw_weekend_cnt = ('weekday', 'count')\n",
    "    ).reset_index()\n",
    "    \n",
    "    tmp_df = tmp_df.merge(tmp_df2, how='left', on='CUST_NO')\n",
    "    tmp_df['trnflw_weekend_cnt'] = tmp_df.trnflw_weekend_cnt.fillna(0)\n",
    "    tmp_df['trnflw_weekend_ratio'] = tmp_df.trnflw_weekend_cnt / tmp_df.trnflw_cust_cnt # 周末交易的比率\n",
    "    \n",
    "    del tmp_df['trnflw_weekday']\n",
    "\n",
    "    return tmp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:26:54.753636Z",
     "iopub.status.busy": "2023-11-13T13:26:54.753463Z",
     "iopub.status.idle": "2023-11-13T13:26:56.346991Z",
     "shell.execute_reply": "2023-11-13T13:26:56.346390Z",
     "shell.execute_reply.started": "2023-11-13T13:26:54.753615Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train = prepro(df_train, end_date_train, 0)\n",
    "df_test = prepro(df_test, end_date_test, -3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:26:56.348260Z",
     "iopub.status.busy": "2023-11-13T13:26:56.348060Z",
     "iopub.status.idle": "2023-11-13T13:26:56.386890Z",
     "shell.execute_reply": "2023-11-13T13:26:56.386400Z",
     "shell.execute_reply.started": "2023-11-13T13:26:56.348236Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>trnflw_trn_cd_nunique</th>\n",
       "      <td>3929.0</td>\n",
       "      <td>1.476203</td>\n",
       "      <td>0.711061</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2.00</td>\n",
       "      <td>6.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_last_date</th>\n",
       "      <td>3929.0</td>\n",
       "      <td>18.066175</td>\n",
       "      <td>20.348797</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>11.000</td>\n",
       "      <td>24.00</td>\n",
       "      <td>9.100000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_mean_amount</th>\n",
       "      <td>3928.0</td>\n",
       "      <td>12463.939966</td>\n",
       "      <td>39490.037701</td>\n",
       "      <td>0.01</td>\n",
       "      <td>1478.07975</td>\n",
       "      <td>3915.285</td>\n",
       "      <td>10038.05</td>\n",
       "      <td>1.394433e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_sum_amount</th>\n",
       "      <td>3929.0</td>\n",
       "      <td>116900.249654</td>\n",
       "      <td>923199.183353</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7310.47000</td>\n",
       "      <td>24981.890</td>\n",
       "      <td>74473.33</td>\n",
       "      <td>5.438287e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_exist_weekend</th>\n",
       "      <td>3929.0</td>\n",
       "      <td>0.722830</td>\n",
       "      <td>0.447658</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_cust_cnt</th>\n",
       "      <td>3929.0</td>\n",
       "      <td>9.426063</td>\n",
       "      <td>13.492877</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>6.000</td>\n",
       "      <td>11.00</td>\n",
       "      <td>2.410000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_weekend_cnt</th>\n",
       "      <td>3929.0</td>\n",
       "      <td>2.647748</td>\n",
       "      <td>4.365683</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>3.00</td>\n",
       "      <td>8.400000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_weekend_ratio</th>\n",
       "      <td>3929.0</td>\n",
       "      <td>0.271727</td>\n",
       "      <td>0.253633</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.250</td>\n",
       "      <td>0.40</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        count           mean            std   min         25%  \\\n",
       "trnflw_trn_cd_nunique  3929.0       1.476203       0.711061  1.00     1.00000   \n",
       "trnflw_last_date       3929.0      18.066175      20.348797  0.00     3.00000   \n",
       "trnflw_mean_amount     3928.0   12463.939966   39490.037701  0.01  1478.07975   \n",
       "trnflw_sum_amount      3929.0  116900.249654  923199.183353  0.00  7310.47000   \n",
       "trnflw_exist_weekend   3929.0       0.722830       0.447658  0.00     0.00000   \n",
       "trnflw_cust_cnt        3929.0       9.426063      13.492877  1.00     3.00000   \n",
       "trnflw_weekend_cnt     3929.0       2.647748       4.365683  0.00     0.00000   \n",
       "trnflw_weekend_ratio   3929.0       0.271727       0.253633  0.00     0.00000   \n",
       "\n",
       "                             50%       75%           max  \n",
       "trnflw_trn_cd_nunique      1.000      2.00  6.000000e+00  \n",
       "trnflw_last_date          11.000     24.00  9.100000e+01  \n",
       "trnflw_mean_amount      3915.285  10038.05  1.394433e+06  \n",
       "trnflw_sum_amount      24981.890  74473.33  5.438287e+07  \n",
       "trnflw_exist_weekend       1.000      1.00  1.000000e+00  \n",
       "trnflw_cust_cnt            6.000     11.00  2.410000e+02  \n",
       "trnflw_weekend_cnt         1.000      3.00  8.400000e+01  \n",
       "trnflw_weekend_ratio       0.250      0.40  1.000000e+00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:26:56.387838Z",
     "iopub.status.busy": "2023-11-13T13:26:56.387664Z",
     "iopub.status.idle": "2023-11-13T13:26:56.433536Z",
     "shell.execute_reply": "2023-11-13T13:26:56.433060Z",
     "shell.execute_reply.started": "2023-11-13T13:26:56.387816Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>trnflw_trn_cd_nunique</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>1.492887</td>\n",
       "      <td>0.730205</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>6.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_last_date</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>17.694573</td>\n",
       "      <td>19.642890</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>8.900000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_mean_amount</th>\n",
       "      <td>47096.0</td>\n",
       "      <td>11648.391780</td>\n",
       "      <td>32657.752974</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1460.2878</td>\n",
       "      <td>3899.412857</td>\n",
       "      <td>9996.854583</td>\n",
       "      <td>2.174533e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_sum_amount</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>116809.363549</td>\n",
       "      <td>846578.277559</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7180.0675</td>\n",
       "      <td>25143.365000</td>\n",
       "      <td>74037.920000</td>\n",
       "      <td>9.136525e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_exist_weekend</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>0.645356</td>\n",
       "      <td>0.478410</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_cust_cnt</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>9.772708</td>\n",
       "      <td>15.964699</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>7.810000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_weekend_cnt</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>2.203533</td>\n",
       "      <td>4.381530</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.970000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_weekend_ratio</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>0.214488</td>\n",
       "      <td>0.236797</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         count           mean            std  min        25%  \\\n",
       "trnflw_trn_cd_nunique  47098.0       1.492887       0.730205  1.0     1.0000   \n",
       "trnflw_last_date       47098.0      17.694573      19.642890  0.0     3.0000   \n",
       "trnflw_mean_amount     47096.0   11648.391780   32657.752974  0.0  1460.2878   \n",
       "trnflw_sum_amount      47098.0  116809.363549  846578.277559  0.0  7180.0675   \n",
       "trnflw_exist_weekend   47098.0       0.645356       0.478410  0.0     0.0000   \n",
       "trnflw_cust_cnt        47098.0       9.772708      15.964699  1.0     3.0000   \n",
       "trnflw_weekend_cnt     47098.0       2.203533       4.381530  0.0     0.0000   \n",
       "trnflw_weekend_ratio   47098.0       0.214488       0.236797  0.0     0.0000   \n",
       "\n",
       "                                50%           75%           max  \n",
       "trnflw_trn_cd_nunique      1.000000      2.000000  6.000000e+00  \n",
       "trnflw_last_date          11.000000     24.000000  8.900000e+01  \n",
       "trnflw_mean_amount      3899.412857   9996.854583  2.174533e+06  \n",
       "trnflw_sum_amount      25143.365000  74037.920000  9.136525e+07  \n",
       "trnflw_exist_weekend       1.000000      1.000000  1.000000e+00  \n",
       "trnflw_cust_cnt            6.000000     11.000000  7.810000e+02  \n",
       "trnflw_weekend_cnt         1.000000      3.000000  1.970000e+02  \n",
       "trnflw_weekend_ratio       0.166667      0.333333  1.000000e+00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-13T13:26:56.434443Z",
     "iopub.status.busy": "2023-11-13T13:26:56.434273Z",
     "iopub.status.idle": "2023-11-13T13:26:56.851938Z",
     "shell.execute_reply": "2023-11-13T13:26:56.851343Z",
     "shell.execute_reply.started": "2023-11-13T13:26:56.434422Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train.to_csv(save_path+'/GSLD_MB_TRNFLW.csv', index=False)\n",
    "df_test.to_csv(save_path+'/GSLD_MB_TRNFLW_{}.csv'.format(stage), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
